package cm.tl.pms.util.fileParser.excel;

import cn.hutool.json.JSONUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.*;

import static cm.tl.pms.util.fileParser.excel.CurrencyConverter.convertToChinese;

/**
 * 新核心excel数据导入处理
 */
public class ExcelDataProcessor {

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class HeaderData {

        /**
         * 客户名称
         */
        @ExcelProperty(value = "客户名称")
        private String projectName;
        /**
         * 业务品种
         */
        private String businessType;
        /**
         * 余额（元）
         */
        private String loanMoney;
//        /**
//         * 流水号
//         */
//        private String serialNo;
        /**
         * 公司代码
         */
        private String companyNo;
        /**
         * 管理主体
         */
        private String manage;
        /**
         * 币种
         */
        private String currency;
        /**
         * 担保类型
         */
        private String guaranteeTypes;
        /**
         * 业务品种
         */
        private String serviceTypes;
        /**
         * 担保单位
         */
        private String guaranteeUnit;
        /**
         * 担保金额(元)
         */
        private String guaranteeMoney;
        /**
         * 抵押人
         */
        private String pledger;
        /**
         * 抵押金额（元）
         */
        private String pledgeMoney;
        /**
         * 五级分类结果
         */
        private String level;
        /**
         * 部门
         */
        private String dept;
        /**
         * 客户所处行业
         */
        private String industry;
    }


    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class ResultData {


        /**
         * 源数据
         */
        List<HeaderData> dataList;

        /**
         * 解析后根据  客户名称/公司代码 分组后的数据
         */
        Map<String, List<HeaderData>> groupedByCustomerAndCompany;

        /**
         * 对业务品种和币种分组求和
         */
        Map<String, Map<String, Map<String, Double>>> groupedByBusinessAndCurrency;

        /**
         * 担保方式统计
         */
        Map<String, Map<String, Integer>> guaranteeTypeStats;

        /**
         * 基本字段 去重合并 ","隔开
         */
        Map<String, Map<String, String>> deduplicateMap;

        /**
         * 基本字段 去重合并 ","隔开 对应的列表 用于获取project表的多
         */
        List<HeaderData> deduplicatedList;


    }


    // 表头对应索引行数（可根据实际情况调整）
    private static int HEADER_ROW_INDEX = 1;

    public static int dataRowStartIndex = HEADER_ROW_INDEX + 1;

    public static void main(String[] args) {
        try {
//            String filePath = "C:\\Users\\Lenovo\\Desktop\\核心数据抓取表格1026.xlsx";
            String filePath = "C:\\Users\\Lenovo\\Documents\\WeChat Files\\wxid_2tswj262d0ye22\\FileStorage\\File\\2024-12\\贷后五级分类担保信息表8月份 (1).xlsx";

            dealExcel(filePath);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 适配解析excel
     *
     * @param filePath
     * @return
     * @throws IOException
     */
    public static ResultData dealExcel(String filePath) throws IOException {
        // 读取Excel文件并处理数据
        List<HeaderData> dataList = readExcelData(filePath, dataRowStartIndex);
        System.out.println("-----原始数据：" + JSONUtil.toJsonStr(dataList));
        Map<String, List<HeaderData>> groupedByCustomerAndCompany = groupByCustomerAndCompany(dataList);

        Map<String, Map<String, Map<String, Double>>> groupedByBusinessAndCurrency = groupByBusinessAndCurrency(groupedByCustomerAndCompany);

        Map<String, Map<String, Integer>> guaranteeTypeStats = countGuaranteeTypes(groupedByCustomerAndCompany);
        System.out.println("**************" + guaranteeTypeStats);

        Map<String, Map<String, String>> deduplicateMap = deduplicateData(groupedByCustomerAndCompany);

        List<HeaderData> deduplicatedList = getDeduplicatedHeaderDataList(deduplicateMap);

        System.out.println("----分组后的数据（客户名称+公司代码）：");
        for (Map.Entry<String, List<HeaderData>> entry : groupedByCustomerAndCompany.entrySet()) {
            System.out.println(entry.getKey() + " : " + entry.getValue());
        }
        System.out.println("按业务品种和币种分组求和后的数据：");
        for (Map.Entry<String, Map<String, Map<String, Double>>> entry : groupedByBusinessAndCurrency.entrySet()) {
            System.out.println(entry.getKey() + " : " + entry.getValue());
        }
        System.out.println("担保方式统计数据：");
        for (Map.Entry<String, Map<String, Integer>> entry : guaranteeTypeStats.entrySet()) {
            System.out.println(entry.getKey() + " : " + entry.getValue());
        }

        System.out.println("基本 统计数据：");
        for (Map.Entry<String, Map<String, String>> entry : deduplicateMap.entrySet()) {
            System.out.println(entry.getKey() + " : " + entry.getValue());
        }
        return new ResultData(dataList, groupedByCustomerAndCompany, groupedByBusinessAndCurrency, guaranteeTypeStats, deduplicateMap, deduplicatedList);
    }


    // 读取Excel数据并转换为HeaderData对象列表
    public static List<HeaderData> readExcelData(String filePath, int dataRowStartIndex) throws IOException {
        List<HeaderData> dataList = new ArrayList<>();
        FileInputStream file = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(file);
        // 使用SXSSFWorkbook来代替XSSFWorkbook，设置内存中最多保留100行，避免内存被占满
//        Workbook workbook = new SXSSFWorkbook(new XSSFWorkbook(file), 1000);
        Sheet sheet = workbook.getSheetAt(0);
        if (sheet == null) {
            throw new IOException("没有找到 sheet 的页面.");
        }
        System.out.println(JSONUtil.toJsonStr(sheet));
        Row headerRow = sheet.getRow(HEADER_ROW_INDEX);
        if (headerRow == null) {
            throw new IOException("没有找到 标题行 .");
        }

        Map<String, Integer> headerIndexMap = getHeaderIndexMap(headerRow);

        for (int i = dataRowStartIndex; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue; // 跳过空行
            }
            String projectName = getCellValue(row.getCell(headerIndexMap.get("客户名称")));
            String companyNo = getCellValue(row.getCell(headerIndexMap.get("公司代码")));
//            System.out.println("-----客户名称-------" + projectName + "++++++公司代码++++++"+ companyNo);
            if (StringUtils.isBlank(projectName) || StringUtils.isBlank(companyNo)) {
                continue;
            }
            HeaderData data = new HeaderData();
            data.setProjectName(projectName);
            data.setCompanyNo(companyNo);
            data.setManage(getCellValue(row.getCell(headerIndexMap.get("管理主体"))));
            data.setBusinessType(getCellValue(row.getCell(headerIndexMap.get("业务品种"))));
            data.setLoanMoney(getCellValue(row.getCell(headerIndexMap.get("余额（元）"))));
            data.setCurrency(getCellValue(row.getCell(headerIndexMap.get("币种"))));
            data.setGuaranteeTypes(getCellValue(row.getCell(headerIndexMap.get("担保类型"))));
            data.setGuaranteeUnit(getCellValue(row.getCell(headerIndexMap.get("担保单位"))));
            data.setServiceTypes(getCellValue(row.getCell(headerIndexMap.get("业务品种"))));
            data.setGuaranteeMoney(getCellValue(row.getCell(headerIndexMap.get("担保金额(元)"))));
            data.setPledger(getCellValue(row.getCell(headerIndexMap.get("抵押人"))));
            data.setPledgeMoney(getCellValue(row.getCell(headerIndexMap.get("抵押金额（元）"))));
            data.setLevel(getCellValue(row.getCell(headerIndexMap.get("五级分类结果"))));
            data.setDept(getCellValue(row.getCell(headerIndexMap.get("部门"))));
            data.setIndustry(getCellValue(row.getCell(headerIndexMap.get("客户所处行业"))));
            dataList.add(data);
        }
        workbook.close();
        file.close();
        return dataList;
    }

    // 获取表头索引的映射关系
    private static Map<String, Integer> getHeaderIndexMap(Row headerRow) {
        Map<String, Integer> headerIndexMap = new HashMap<>();
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            Cell cell = headerRow.getCell(i);
            if (cell != null) {
                headerIndexMap.put(cell.getStringCellValue(), i);
            }
        }
        return headerIndexMap;
    }

    // 根据客户名称和公司代码分组
    public static Map<String, List<HeaderData>> groupByCustomerAndCompany(List<HeaderData> dataList) {
        Map<String, List<HeaderData>> groupedMap = new HashMap<>();
        for (HeaderData data : dataList) {
//            String key = data.getProjectName() + data.getCompanyNo();
            String key = data.getProjectName() + "-基本信息";
            if (!groupedMap.containsKey(key)) {
                groupedMap.put(key, new ArrayList<>());
            }
            groupedMap.get(key).add(data);
        }
        return groupedMap;
    }

    // 对业务品种和币种分组求和
    public static Map<String, Map<String, Map<String, Double>>> groupByBusinessAndCurrency(Map<String, List<HeaderData>> groupedByCustomerAndCompany) {
        Map<String, Map<String, Map<String, Double>>> resultMap = new HashMap<>();
        for (Map.Entry<String, List<HeaderData>> entry : groupedByCustomerAndCompany.entrySet()) {
            List<HeaderData> dataList = entry.getValue();
            for (HeaderData data : dataList) {
                String basicInfo = data.getProjectName() + "-基本信息";
                String businessType = data.getBusinessType();
                String currency = convertToChinese(data.getCurrency());
                double loanMoney = Double.parseDouble(data.getLoanMoney().replace(",", ""));
                if (!resultMap.containsKey(basicInfo)) {
                    resultMap.put(basicInfo, new HashMap<>());
                }
                if (!resultMap.get(basicInfo).containsKey(businessType)) {
                    resultMap.get(basicInfo).put(businessType, new HashMap<>());
                }
                if (!resultMap.get(basicInfo).get(businessType).containsKey(currency)) {
                    resultMap.get(basicInfo).get(businessType).put(currency, 0.0);
                }

                double sum = resultMap.get(basicInfo).get(businessType).get(currency) + loanMoney;
                // 使用 DecimalFormat 格式化结果
                DecimalFormat df = new DecimalFormat("0.00");
                String formattedSum = df.format(sum);
                System.out.println("业务品种：" + businessType + "，币种：" + currency + "，求和：" + sum + "，Double.valueOf：" + formattedSum);
                resultMap.get(basicInfo).get(businessType).put(currency, sum);
            }
        }
        return resultMap;
    }


    private static Map<String, Map<String, String>> deduplicateData(Map<String, List<HeaderData>> groupedByCustomerAndCompany) {
        Map<String, Map<String, String>> deduplicatedMap = new HashMap<>();
        for (Map.Entry<String, List<HeaderData>> entry : groupedByCustomerAndCompany.entrySet()) {
//            String key = entry.getKey();
            String key = entry.getKey();
            List<HeaderData> dataList = entry.getValue();
            Map<String, String> fieldValueMap = new HashMap<>();
            for (HeaderData data : dataList) {
                // 处理除业务品种和币种外的每个字段
                updateFieldValueMap(fieldValueMap, "客户名称", data.getProjectName());
                updateFieldValueMap(fieldValueMap, "管理主体", data.getManage());
                updateFieldValueMap(fieldValueMap, "担保类型", data.getGuaranteeTypes());
                updateFieldValueMap(fieldValueMap, "担保单位", data.getGuaranteeUnit());
                updateFieldValueMap(fieldValueMap, "业务品种", data.getServiceTypes());
//                updateFieldValueMap(fieldValueMap, "担保金额(元)", data.getGuaranteeMoney());
                updateFieldValueMap(fieldValueMap, "抵押人", data.getPledger());
//                updateFieldValueMap(fieldValueMap, "抵押金额（元）", data.getPledgeMoney());
                updateFieldValueMap(fieldValueMap, "五级分类结果", data.getLevel());
                updateFieldValueMap(fieldValueMap, "部门", data.getDept());
                updateFieldValueMap(fieldValueMap, "客户所处行业", data.getIndustry());
//                updateFieldValueMap(fieldValueMap, "流水号", data.getSerialNo());
                updateFieldValueMap(fieldValueMap, "公司代码", data.getCompanyNo());
//                updateFieldValueMap(fieldValueMap, "余额（元）", data.getLoanMoney());
            }
            deduplicatedMap.put(key, fieldValueMap);
        }
        return deduplicatedMap;
    }

    private static void updateFieldValueMap(Map<String, String> fieldValueMap, String fieldName, String value) {
        if (fieldValueMap.containsKey(fieldName)) {
            String existingValue = fieldValueMap.get(fieldName);
            if (Objects.isNull(value)) {
                value = "";
            }
            if (Objects.isNull(existingValue)) {
                existingValue = "";
            }
            if (!existingValue.contains(value)) {
                if (StringUtils.isBlank(existingValue)) {
                    fieldValueMap.put(fieldName, value);
                } else {
                    fieldValueMap.put(fieldName, existingValue + "," + value);
                }

            }
        } else {
            fieldValueMap.put(fieldName, value);
        }
    }

    // 统计担保方式
    public static Map<String, Map<String, Integer>> countGuaranteeTypes(Map<String, List<HeaderData>> groupedByCustomerAndCompany) {
        Map<String, Map<String, Integer>> resultMap = new HashMap<>();
        String basicInfo = "基本信息";
        String guaranteeSituation = "借款人担保情况";
        // 定义担保方式集合
        String[] guaranteeTypes = {"信用", "质押", "连带责任保证", "一般保证", "抵押", "保证"};
        for (Map.Entry<String, List<HeaderData>> entry : groupedByCustomerAndCompany.entrySet()) {
            List<HeaderData> dataList = entry.getValue();
//            System.out.println("---------1-----"+JSONUtil.toJsonStr(dataList));
            for (HeaderData data : dataList) {
                String guaranteeType = data.getGuaranteeTypes();
                String projectName = data.getProjectName();
                String resultKey = projectName + "-" + basicInfo;
                if (!resultMap.containsKey(resultKey)) {
                    resultMap.put(resultKey, new HashMap<>());
                }
                for (String type : guaranteeTypes) {
                    String key = "担保方式-" + type;
                    String key2 = "担保方式-" + guaranteeType;
                    Map<String, Integer> subMap = resultMap.get(resultKey);
                    if (!subMap.containsKey(key)) {
                        subMap.put(key, 0);
                    }
                    if (type.equals(guaranteeType)) {
                        subMap.put(key, 1);
                    } else if (StringUtils.isNotBlank(guaranteeType)) {
                        subMap.put(key2, 1);
                    }

                    if (subMap.getOrDefault("担保方式-" + "连带责任保证", 0) == 1
                            ||
                            subMap.getOrDefault("担保方式-" + "一般保证", 0) == 1

                    ) {
                        subMap.put("担保方式-" + "保证", 1);
                    }
                }

            }
        }
        return resultMap;
    }


    private static List<HeaderData> getDeduplicatedHeaderDataList(Map<String, Map<String, String>> deduplicatedMap) {
        List<HeaderData> headerDataList = new ArrayList<>();
        for (Map.Entry<String, Map<String, String>> entry : deduplicatedMap.entrySet()) {
            String key = entry.getKey();
            Map<String, String> fieldValueMap = entry.getValue();
            HeaderData data = new HeaderData();
            data.setProjectName(fieldValueMap.get("客户名称"));
            data.setBusinessType(null);
            data.setCurrency(null);
            data.setManage(fieldValueMap.get("管理主体"));
            data.setGuaranteeTypes(fieldValueMap.get("担保类型"));
            data.setGuaranteeUnit(fieldValueMap.get("担保单位"));
            data.setGuaranteeMoney(null);
            data.setPledger(fieldValueMap.get("抵押人"));
            data.setPledgeMoney(null);
            data.setLevel(fieldValueMap.get("五级分类结果"));
            data.setDept(fieldValueMap.get("部门"));
            data.setIndustry(fieldValueMap.get("客户所处行业"));
//            data.setSerialNo(null);
            data.setCompanyNo(fieldValueMap.get("公司代码"));
            data.setLoanMoney(null);
            headerDataList.add(data);
        }
        return headerDataList;
    }


    // 获取单元格的值并处理为空的情况
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                // 将数字单元格转换为字符串
                cell.setCellType(CellType.STRING);
                return String.valueOf(cell.getStringCellValue());
            case BOOLEAN:
                // 将数字单元格转换为字符串
//                    cell.setCellType(CellType.STRING);
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }


}